# coding:utf-8
import set_env_path

import os

import xlrd
from sqlalchemy import create_engine
from sqlalchemy.sql import text


engine = create_engine(
    os.getenv('DATABASE_URL')
    or 'mysql+pymysql://root:@127.0.0.1/wph_www_prod?charset=utf8'
)
conn = engine.connect()


def handle():
    """
    created by chenhj at 2018/7/12

    """
    book = xlrd.open_workbook('./pay_user_bid_cover2.xlsx')
    sheet = book.sheet_by_index(0)

    select_sql = text("""
        select id from user where mobile=:mobile
    """)
    update_sql = text("""
        update pack set bid_point=:bid_point where uid=:uid
    """)

    for row in range(1, sheet.nrows):
        mobile = str(sheet.cell(row, 0).value).strip().split('.')[0]
        new_bid_point = int(sheet.cell(row, 2).value)

        print(mobile, new_bid_point)

        user = conn.execute(select_sql, dict(mobile=mobile)).fetchone()
        if user:
            conn.execute(update_sql, dict(bid_point=new_bid_point, uid=user.id))


if __name__ == '__main__':
    handle()
